﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace WebSite.NewsHandle
{
    /// <summary>
    ///用来进行文章的添加，修改，删除，查看
    /// </summary>
    public class NewsHandler
    {
        SqlConnection conn;
        public NewsHandler(string ConnectionString)
        {
            conn = new SqlConnection(ConnectionString);
            conn.Open();
        }
        /// <summary>
        /// 获得第PageSize页的文章列表内容
        /// </summary>
        /// <param name="PageSize">页面大小</param>
        /// <param name="PageIndex">页数</param>
        /// <returns>返回存储数据的DataSet</returns>
        public DataSet GetAllNews(int PageSize,int PageIndex)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = conn;
                da.SelectCommand.CommandText = "GetAllNews";
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.Add(new SqlParameter("@PageSize", PageSize));
                da.SelectCommand.Parameters.Add(new SqlParameter("@PageIndex", PageIndex));
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 获得不区分文章类别的文章数量
        /// </summary>
        /// <returns>文章数量</returns>
        public int GetCountOfAllNews()
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "GetCountOfAllNews";
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter para = new SqlParameter("@Count", SqlDbType.Int);
                para.Direction = ParameterDirection.Output;
                command.Parameters.Add(para);
                command.ExecuteNonQuery();
                return (int)command.Parameters["@Count"].Value;
            }
            catch { throw; }

        }
        public int GetCountOfSectionNews(int NewsSection)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = "GetCountOfSectionNews";
                command.Connection = conn;
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter para = new SqlParameter("@Count", SqlDbType.Int);
                para.Direction = ParameterDirection.Output;
                command.Parameters.Add(para);
                command.Parameters.Add(new SqlParameter("@NewsSection", NewsSection));
                command.ExecuteNonQuery();
                return (int)command.Parameters["@Count"].Value;
            }
            catch { throw; }
        }
        /// <summary>
        /// 返回所有文章列表
        /// </summary>
        /// <returns>存有文章数据的DataSet</returns>
        public DataSet GetAllNews()
        {
            return GetAllNews(0,0);
        }
        /// <summary>
        /// 通过文章id获得文章列表
        /// </summary>
        /// <param name="NewsId">文章Id</param>
        /// <returns>存储数据的DataSet</returns>
        public DataSet GetNewsById(int NewsId)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand();
                da.SelectCommand.Connection = conn;
                da.SelectCommand.CommandText = "GetNewsByNewsId";
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@NewsId", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = NewsId;
                da.SelectCommand.Parameters.Add(param);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 通过文章的所属Section获得第PageIndex页的文章列表，0为新闻，1为重要通知，2为健康知识
        /// </summary>
        /// <param name="NewsSection">文章所述Section,0新闻，1重要通知，2健康知识</param>
        /// <param name="PageSize">页面大小</param>
        /// <param name="PageIndex">页数</param>
        /// <returns>存储数据的DataSet</returns>
        public DataSet GetNewBySection(int NewsSection,int PageSize,int PageIndex)
        {
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(new SqlCommand());
                da.SelectCommand.Connection = conn;
                da.SelectCommand.CommandText = "GetNewsBySection";
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@NewsSection", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = NewsSection;
                da.SelectCommand.Parameters.Add(param);
                param = new SqlParameter("@PageSize",PageSize);
                da.SelectCommand.Parameters.Add(param);
                da.SelectCommand.Parameters.Add(new SqlParameter("@PageIndex", PageIndex));
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 获得通过文章的所属Section获得文章列表，0为新闻，1为重要通知，2为健康知识
        /// </summary>
        /// <param name="NewsSection">文章所述类别</param>
        /// <returns>存储数据的数据集</returns>
        public DataSet GetNewBySection(int NewsSection)
        {
            return GetNewBySection(NewsSection,0,0);
        }
        /// <summary>
        /// 添加文章
        /// </summary>
        /// <param name="NewsSection">0新闻，1重要通知，2健康知识</param>
        /// <param name="UserId">作者的Id</param>
        /// <param name="Title">标题</param>
        /// <param name="Content">新闻内容</param>
        /// <param name="date">添加时间</param>
        /// <returns>添加成功返回news的ID，否则返回空字符串</returns>
        public string AddNews(int NewsSection, string UserId, string Title, string Content, DateTime date)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "AddNews";
                command.CommandType = CommandType.StoredProcedure;
                //添加输入参数列表
                SqlParameter[] ArrayParams = {
                                   new SqlParameter("@NewsSection",NewsSection),
                                   new SqlParameter("@UserId",UserId),
                                   new SqlParameter("@Title",Title),
                                   new SqlParameter("@Content",Content),
                                   new SqlParameter("@Date",date),
                               };
                foreach (SqlParameter param in ArrayParams)
                {
                    command.Parameters.Add(param);
                }
                SqlParameter para = new SqlParameter("@ReturnValue", SqlDbType.Int);
                para.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(para);
                para = new SqlParameter("@NewsId", SqlDbType.Int);
                para.Direction = ParameterDirection.Output;
                command.Parameters.Add(para);
                command.ExecuteNonQuery();
                if ((int)command.Parameters["@ReturnValue"].Value == 1)
                {
                    return command.Parameters["@NewsId"].Value.ToString();
                }
                else return "";
                
            }
            catch
            {
                throw;
            }

        }
        /// <summary>
        /// 修改文章
        /// </summary>
        /// <param name="NewsId">文章id</param>
        /// <param name="NewsSection">文章所属类别</param>
        /// <param name="UserId">用户id</param>
        /// <param name="Title">标题</param>
        /// <param name="Content">内容</param>
        /// <param name="date">更改时间</param>
        /// <returns>成功返回true，否则返回false</returns>
        public bool ModifyNews(int NewsId, int NewsSection, string UserId, string Title, string Content, DateTime date)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "ModifyNews";
                command.CommandType = CommandType.StoredProcedure;
                //添加输入参数列表
                SqlParameter[] ArrayParams = {
                                   new SqlParameter("@NewsSection",NewsSection),
                                   new SqlParameter("@UserId",UserId),
                                   new SqlParameter("@Title",Title),
                                   new SqlParameter("@Content",Content),
                                   new SqlParameter("@Date",date),
                                   new SqlParameter("@NewsId",NewsId)
                               };
                foreach (SqlParameter param in ArrayParams)
                {
                    command.Parameters.Add(param);
                }
                SqlParameter para = new SqlParameter("@ReturnValue", SqlDbType.Int);
                para.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(para);
                command.ExecuteNonQuery();
                if ((int)para.Value == 1)
                {
                    return true;
                }
                else return false;
            }
            catch
            {
                throw;
            }
        }
        /// <summary>
        /// 删除文章
        /// </summary>
        /// <param name="NewsId">文章Id号</param>
        /// <returns>成功返回true，否则返回false</returns>
        public bool DeleteNews(int NewsId)
        {
            try
            {
                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                command.CommandText = "DeleteNews";
                command.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@NewsId", NewsId);
                param.Direction = ParameterDirection.Input;
                command.Parameters.Add(param);
                param = new SqlParameter("@ReturnValue", SqlDbType.Int);
                param.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(param);
                command.ExecuteNonQuery();
                if ((int)param.Value == 1)
                {
                    return true;
                }
                else return false;
            }
            catch
            {
                throw;
            }
        }
        public static void main()
        {
            NewsHandler newsHandler = new NewsHandler("Data Source=Localhost;Integrated Security=SSPI;Initial Catalog=CSHDB");
            DataSet ds;
            int sectionid = 0;
            string uid = "0411211";
            string Title = "杨建祥在测试啦";
            string Content = "如果要是能成功就好了";
            DateTime date = DateTime.Now;
            newsHandler.AddNews(sectionid, uid, Title, Content, date);
            ds = newsHandler.GetAllNews();
            for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    Console.Write(ds.Tables[0].Rows[j][i].ToString() + "   ");
                }
                Console.Write("\n");
            }

        }
    }
}
